package no.ntnu.tdt4237;

import com.sun.tools.internal.xjc.reader.xmlschema.bindinfo.BIConversion.Static;
import java.sql.*;
import java.util.*;
import java.util.Date;

import no.ntnu.tdt4237.helperactions.DateHelpers;

/*
 *  Layer for interaction between the model classes 
 *  and the database
 */
public class Database {

    private static Connection createConnection() {
        Connection conn;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            //conn = DriverManager.getConnection("jdbc:mysql://mysql.stud.ntnu.no/" + Config.SQL_DB, Config.SQL_USERNAME, Config.SQL_PASSWORD);
            conn = DriverManager.getConnection("jdbc:mysql://localhost/" + Config.SQL_DB, Config.SQL_USERNAME, Config.SQL_PASSWORD);
        } catch (Exception e) {
            e.printStackTrace();
            conn = null;
        }

        return conn;
    }

    public static boolean login(String username, String password) {

        Connection conn = createConnection();

        boolean authenticated = false;

        try {
            //Statement statement = conn.createStatement();
            String selectString = "SELECT * FROM BlogUser WHERE username=? AND password=? AND active=?";
            PreparedStatement query = conn.prepareStatement(selectString);
            query.setString(1, username);
            query.setString(2, password);
            query.setInt(3, 1);
            ResultSet resultSet = query.executeQuery();

            //String query = "SELECT * FROM BlogUser WHERE username='" + username + "' AND password='" + password + "';";
            //ResultSet resultSet  = statement.executeQuery(query);
            if (resultSet.next()) {

                authenticated = true;
            }
            resultSet.close();
            query.close();
            //       statement.close();
            conn.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return authenticated;
    }

    public static boolean saveUser(User u, String oldPassword) {
        Connection conn = createConnection();

        boolean result;

        if (u != null) {
            if (!userExist(u)) {
                try {
                    //Statement st = conn.createStatement();

                    String insertString = "INSERT INTO BlogUser(username,password,email,fname,lname,active,code) VALUES(?,?,?,?,?,?,?)";
                    PreparedStatement query = conn.prepareStatement(insertString);
                    query.setString(1, u.getUserName());
                    query.setString(2, u.getPassword());
                    query.setString(3, u.getEmail());
                    query.setString(4, u.getFirstName());
                    query.setString(5, u.getLastName());
                    query.setInt(6, 0);
                    String activation = UUID.randomUUID().toString().substring(0, 32);
//                    System.out.println("UUID: " + activation + " Length: " + activation.length());
                    query.setString(7, activation);
                    result = query.execute();

                    // String query = "INSERT INTO BlogUser(username,password,email,fname,lname) VALUES('"+ u.getUserName() +"','"+u.getPassword()+"','"+u.getEmail()+"','"+u.getFirstName()+"','"+u.getLastName()+"');";
                    // st.executeUpdate(query);

                    //st.close();
                    query.close();
                    conn.close();
                    if (MailSender.isValidEmail(u.getEmail())) {
                        MailSender.sendAvtivationCode(u.getEmail(), activation);
                    }
                    return true;
                } catch (Exception e) {
                    e.printStackTrace();
                    return false;
                }
            } else {
                try {
//                    System.out.println("Verdi av brukernavn: " + u.getUserName());

                    //Statement st = conn.createStatement();

                    String selectQuery = "SELECT password FROM BlogUser WHERE username = ?";
                    PreparedStatement query = conn.prepareStatement(selectQuery);


                    query.setString(1, u.getUserName());

                    ResultSet rs = query.executeQuery();

                    if (rs.next()) {
                        String dboldPassword = rs.getString("password");
//                        System.out.println("Verdi av dboldPassword: " + dboldPassword);
                        if (oldPassword.equals(dboldPassword)) {

                            String updateString = "UPDATE BlogUser SET password=?, email=?, fname=?, lname=? WHERE username=?";
                            query = conn.prepareStatement(updateString);
                            query.setString(1, u.getPassword());
                            query.setString(2, u.getEmail());
                            query.setString(3, u.getFirstName());
                            query.setString(4, u.getLastName());
                            query.setString(5, u.getUserName());

                            //String query = "UPDATE BlogUser SET password='"+u.getPassword()+"', email='"+u.getEmail()+"', fname='"+u.getFirstName()+"', lname='"+u.getLastName()+"' WHERE username='"+u.getUserName()+"';";
                            //st.executeUpdate(query);

                            query.executeUpdate();
                            query.close();
                            conn.close();
                            return true;
                        } else {
                            query.close();
                            conn.close();
                            return false;
                        }
                    }

                    //st.close();
                    query.close();
                    conn.close();
                    return false;

                } catch (Exception e) {
                    e.printStackTrace();
                    return false;
                }
            }
        } else {
            return false;
        }
    }

    public static boolean saveUser(User u) {
        Connection conn = createConnection();

        boolean result;

        if (u != null) {
            if (!userExist(u)) {
                try {
                    //Statement st = conn.createStatement();

                    String insertString = "INSERT INTO BlogUser(username,password,email,fname,lname,active,code) VALUES(?,?,?,?,?,?,?)";
                    PreparedStatement query = conn.prepareStatement(insertString);
                    query.setString(1, u.getUserName());
                    query.setString(2, u.getPassword());
                    query.setString(3, u.getEmail());
                    query.setString(4, u.getFirstName());
                    query.setString(5, u.getLastName());
                    query.setInt(6, 0);
                    String activation = UUID.randomUUID().toString().substring(0, 32);
//                    System.out.println("UUID: " + activation + " Length: " + activation.length());
                    query.setString(7, activation);
                    result = query.execute();

                    // String query = "INSERT INTO BlogUser(username,password,email,fname,lname) VALUES('"+ u.getUserName() +"','"+u.getPassword()+"','"+u.getEmail()+"','"+u.getFirstName()+"','"+u.getLastName()+"');";
                    // st.executeUpdate(query);

                    //st.close();
                    query.close();
                    conn.close();
                    if (MailSender.isValidEmail(u.getEmail())) {
                        MailSender.sendAvtivationCode(u.getEmail(), activation);
                    }
                    result = true;
                } catch (Exception e) {
                    e.printStackTrace();
                    result = false;
                }
            } else {
                try {
                    //Statement st = conn.createStatement();

                    String updateString = "UPDATE BlogUser SET password=?, email=?, fname=?, lname=? WHERE username=?";
                    PreparedStatement query = conn.prepareStatement(updateString);
                    query.setString(1, u.getPassword());
                    query.setString(2, u.getEmail());
                    query.setString(3, u.getFirstName());
                    query.setString(4, u.getLastName());
                    query.setString(5, u.getUserName());

                    //String query = "UPDATE BlogUser SET password='"+u.getPassword()+"', email='"+u.getEmail()+"', fname='"+u.getFirstName()+"', lname='"+u.getLastName()+"' WHERE username='"+u.getUserName()+"';";
                    //st.executeUpdate(query);

                    query.executeUpdate();

                    //st.close();
                    query.close();
                    conn.close();
                    result = true;

                } catch (Exception e) {
                    e.printStackTrace();
                    result = false;
                }
            }
        } else {
            result = false;
        }

        return result;
    }

    private static boolean userExist(User u) {
        ArrayList<User> users = getUsers();

        boolean result = false;

        for (User user : users) {
            if (user.getUserName().equals(u.getUserName())) {
                result = true;
            }
        }

        return result;
    }

    public static ArrayList<User> getUsers() {
        ArrayList<User> list = new ArrayList<User>();

        Connection conn = createConnection();

        try {


            Statement st = conn.createStatement();

            String query = "SELECT * FROM BlogUser;";
            ResultSet rs = st.executeQuery(query);

            while (rs.next()) {
                list.add(new User(rs.getString("username"), rs.getString("password"), rs.getString("email"), rs.getString("fname"), rs.getString("lname")));
            }

            rs.close();
            st.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

        return list;

    }

    public static User getUser(String username) {
        ArrayList<User> users = getUsers();
        User result = null;

        for (User u : users) {
            if (u.getUserName().equals(username)) {
                result = u;
            }
        }

        return result;
    }
    
    public static boolean savePost(BlogPost p) {
        Connection conn = createConnection();

        boolean result;

        if (p != null) {
            if (!postExist(p)) {
                try {

                    /*
                    String updateString = "UPDATE BlogUser SET password=?, email=?, fname=?, lname=? WHERE username=?";
                    PreparedStatement query = conn.prepareStatement(updateString);
                    query.setString(1, u.getPassword());

                     */

                    String updateString = "INSERT INTO BlogPost SET title=?, text=?,date=?,owner=?,picture=?";
                    PreparedStatement query2 = conn.prepareStatement(updateString);
                    query2.setString(1, p.getTitle());
                    query2.setString(2, p.getText());
                    query2.setString(3, "" + DateHelpers.utilDateToSqlDate(p.getDate()));
                    query2.setString(4, p.getOwner().getUserName());
                    query2.setString(5, p.getPictureName());

                    query2.executeUpdate();
                    query2.close();
                    result = true;

                    /*

                     *
                    Statement st = conn.createStatement();

                    String query = "INSERT INTO BlogPost(title,text,date,owner,picture) "
                    + "VALUES('" + p.getTitle() + "','" + p.getText() + "','" + DateHelpers.utilDateToSqlDate(p.getDate())
                    + "','" + p.getOwner().getUserName() + "','" + p.getPictureName() + "');";
                    st.executeUpdate(query);

                    st.close();
                    conn.close();
                    result = true;
                     * */


                } catch (Exception e) {
                    e.printStackTrace();
                    result = false;
                }
            } else {
                try {
                    String updateString = "UPDATE BlogPost SET title=?, text=?,date=?,owner=?,picture=?, WHERE blogid=?";
                    PreparedStatement query2 = conn.prepareStatement(updateString);
                    query2.setString(1, p.getTitle());
                    query2.setString(2, p.getText());
                    query2.setString(3, "" + DateHelpers.utilDateToSqlDate(p.getDate()));
                    query2.setString(4, p.getOwner().getUserName());
                    query2.setString(5, p.getPictureName());
                    query2.setString(6, "" + getPostId(p));

                    query2.executeUpdate();
                    query2.close();
                    result = true;
                    /*
                    Statement st = conn.createStatement();

                    String query = "UPDATE BlogPost SET text='" + p.getText() + "', date='" + DateHelpers.utilDateToSqlDate(p.getDate()) + "'  WHERE blogid='" + getPostId(p) + "';";
                    st.executeUpdate(query);

                    st.close();
                    conn.close();
                    result = true;
                     * (
                     */
                    result = true;
                } catch (Exception e) {
                    e.printStackTrace();
                    result = false;
                }
            }
        } else {
            result = false;
        }

        return result;
    }

    public static boolean editPost(BlogPost oldPos, BlogPost newPost) {
        if (oldPos != null && newPost != null) {
            int postId = getPostId(oldPos);
            if (postId >= 0) {
                Connection conn = createConnection();
                boolean result = false;
                try {

                    String updateString = "UPDATE BlogPost SET text=?, date=? WHERE blogid =?";
                    PreparedStatement query2 = conn.prepareStatement(updateString);
                    query2.setString(1, newPost.getText());
                    query2.setString(2, "" + DateHelpers.utilDateToSqlDate(newPost.getDate()));
                    query2.setString(3, "" + postId);


                    query2.executeUpdate();
                    query2.close();

                    /*

                    Statement st = conn.createStatement();

                    String query = "UPDATE BlogPost SET text='" + newPost.getText() + "', date='" + DateHelpers.utilDateToSqlDate(newPost.getDate()) + "'"
                    + "  WHERE blogid='" + postId + "';";
                    st.executeUpdate(query);

                    st.close();
                    conn.close();
                     */
                    result = true;

                } catch (Exception e) {
                    System.out.println("" + e);
                    e.printStackTrace();

                    result = false;
                }
                return result;
            }

        }
        return false;
    }

    public static boolean deletePost(BlogPost p) {
        Connection conn = createConnection();

        boolean result;

        if (p != null) {
            if (postExist(p)) {
                try {

                    String updateString = "DELETE FROM BlogPost WHERE title=? AND date=? AND owner=?";

//                    System.out.println("t");
                    PreparedStatement query2 = conn.prepareStatement(updateString);
                    query2.setString(1, p.getTitle());
                    query2.setString(2, "" + DateHelpers.utilDateToSqlDate(p.getDate()));
                    query2.setString(3, "" + p.getOwner().getUserName());

                    query2.execute();
                    query2.close();

                    return true;

                    /*


                    Statement st = conn.createStatement();

                    String query = "DELETE FROM `BlogPost` WHERE `title` = '" + p.getTitle() + "' AND "
                    + "`date` = '" + DateHelpers.utilDateToSqlDate(p.getDate()) + "' AND `owner` = '" + p.getOwner().getUserName() + "' LIMIT 1;";
                    st.execute(query);
                    result = st.getUpdateCount() == 1;
                    st.close();
                    conn.close();
                     */

                } catch (Exception e) {
                    System.out.println("ERROR: " + e + "\n");
                    e.printStackTrace();
                    result = false;
                }
            } else {
                result = false;
            }
        } else {
            result = false;
        }

        return result;
    }

    public static boolean deleteComment(Comment c) {
        Connection conn = createConnection();

        boolean result;

        if (c != null) {
            int postId = getPostId(c.getPost());
            if (postId >= 0) {
                try {

                    PreparedStatement query2;
                    if (c.getOwner() != null) {
                        String updateString = "DELETE FROM BlogComment WHERE text=? AND date=? AND owner=? AND postId=?";
                        query2 = conn.prepareStatement(updateString);
                        //PreparedStatement query2 = conn.prepareStatement(updateString);
                        query2.setString(1, c.getText());
                        query2.setString(2, "" + DateHelpers.utilDateToSqlDate(c.getDate()));
                        query2.setString(3, c.getOwner().getUserName());
                        query2.setString(4, "" + postId);

                        query2.execute();
                        query2.close();
                    } else {
                        String updateString = "DELETE FROM BlogComment WHERE text=? AND date=? AND owner=? AND postId=?";

                        query2 = conn.prepareStatement(updateString);
                        query2.setString(1, c.getText());
                        query2.setString(2, "" + DateHelpers.utilDateToSqlDate(c.getDate()));
                        ;
                        query2.setString(3, c.getAnonymous());
                        query2.setString(4, "" + postId);

                        query2.execute();
                        query2.close();
                    }
                    /*

                    Statement st = conn.createStatement();

                    String query = "DELETE FROM `BlogComment` WHERE `text` = '" + c.getText() + "' AND "
                    + "`date` = '" + DateHelpers.utilDateToSqlDate(c.getDate()) + "' AND "
                    + "`owner` = '" + c.getOwner().getUserName() + "' AND `postid` = '" + postId + "' LIMIT 1;";
                    st.execute(query);
                    result = st.getUpdateCount() == 1;
                    st.close();
                    conn.close();
                     * */
                    result = query2.getUpdateCount() == 1;
                } catch (Exception e) {
                    e.printStackTrace();
                    result = false;
                }
            } else {
                result = false;
            }
        } else {
            result = false;
        }

        return result;
    }

    public static ArrayList<BlogPost> getPostForUser(User u) {
        ArrayList<BlogPost> list = new ArrayList<BlogPost>();

        Connection conn = createConnection();

        try {

            String updateString = "SELECT * FROM BlogPost WHERE owner=?";

            PreparedStatement query2 = conn.prepareStatement(updateString);
            query2.setString(1, u.getUserName());

            ResultSet rs = query2.executeQuery();


            while (rs.next()) {
                list.add(new BlogPost(rs.getInt("blogid"), rs.getString("title"), rs.getString("text"), rs.getDate("date"), getUser(rs.getString("owner")), rs.getString("picture")));
            }

            query2.close();

            /*


            Statement st = conn.createStatement();

            String query = "SELECT * FROM BlogPost WHERE owner='" + u.getUserName() + "';";
            ResultSet rs = st.executeQuery(query);

            while (rs.next()) {
            list.add(new BlogPost(rs.getString("title"), rs.getString("text"), rs.getDate("date"), getUser(rs.getString("owner")), rs.getString("picture")));
            }
             *
             */

            rs.close();
            query2.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

        return list;
    }

    public static boolean saveComment(Comment c) {
        Connection conn = createConnection();

        boolean result;

        if (c != null) {

            try {


                String updateString = "INSERT INTO BlogComment(owner,text,date,postid) VALUES(?,?,?,?)";

                if (c.getOwner() != null) {
                    PreparedStatement query2 = conn.prepareStatement(updateString);
                    query2.setString(1, c.getOwner().getUserName());
//                    System.out.println("" + c.getOwner().getUserName());
                    query2.setString(2, "" + c.getText());
                    query2.setString(3, "" + DateHelpers.utilDateToSqlDate(c.getDate()));
                    query2.setString(4, "" + getPostId(c.getPost()));

                    query2.execute();
                    query2.close();
                } else {
                    PreparedStatement query2 = conn.prepareStatement(updateString);
                    query2.setString(1, c.getAnonymous());
//                    System.out.println("" + c.getAnonymous());
                    query2.setString(2, "" + c.getText());
                    query2.setString(3, "" + DateHelpers.utilDateToSqlDate(c.getDate()));
                    query2.setString(4, "" + getPostId(c.getPost()));

                    query2.execute();
                    query2.close();
                }


                /*
                Statement st = conn.createStatement();

                String query = "INSERT INTO BlogComment(owner,text,date,postid) VALUES('" + c.getOwner().getUserName() + "','" + c.getText() + "','" + DateHelpers.utilDateToSqlDate(c.getDate()) + "','" + getPostId(c.getPost()) + "');";
                st.executeUpdate(query);

                st.close();
                 */

                conn.close();
                result = true;

            } catch (Exception e) {
                e.printStackTrace();
                result = false;
            }
        } else {
            result = false;
        }

        return result;
    }

    private static int getPostId(BlogPost p) {

        int res = -1;
        Connection conn = createConnection();

        try {
            Statement st = conn.createStatement();


            String getPostIdString = "SELECT * FROM BlogPost WHERE owner=?";
            PreparedStatement query2 = conn.prepareStatement(getPostIdString);
            query2.setString(1, p.getOwner().getUserName());
            ResultSet rs = query2.executeQuery();


            /*
            String query = "SELECT * FROM BlogPost WHERE owner='" + p.getOwner().getUserName() + "';";
            ResultSet rs = st.executeQuery(query);
             */
            while (rs.next()) {
                int id = rs.getInt("blogid");
                String title = rs.getString("title");
                Date date = rs.getDate("date");
                String username = rs.getString("owner");

                boolean titleMatch = title.equals(p.getTitle());
                boolean dateMatch = DateHelpers.compareDates(DateHelpers.utilDateToSqlDate(p.getDate()), date);
                boolean userMatch = username.equals(p.getOwner().getUserName());

                if (titleMatch && dateMatch && userMatch) {
                    res = id;
                }
            }

            rs.close();
            st.close();
            query2.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

        return res;
    }

    private static boolean postExist(BlogPost p) {
        boolean res = false;

//        System.out.println(p.toString());

        Connection conn = createConnection();

        try {
            Statement st = conn.createStatement();

            String preparedQuery = "SELECT * FROM BlogPost WHERE owner = ?";
            PreparedStatement query = conn.prepareStatement(preparedQuery);

            query.setString(1, p.getOwner().getUserName());

//            String query = "SELECT * FROM BlogPost WHERE owner='" + p.getOwner().getUserName() + "';";
//            ResultSet rs = st.executeQuery(query);

            ResultSet rs = query.executeQuery();

            while (rs.next()) {
                String title = rs.getString("title");
                Date date = rs.getDate("date");
                String username = rs.getString("owner");

                boolean titleMatch = title.equals(p.getTitle());
                boolean dateMatch = DateHelpers.compareDates(DateHelpers.utilDateToSqlDate(p.getDate()), date);
                boolean userMatch = username.equals(p.getOwner().getUserName());

                if (titleMatch && dateMatch && userMatch) {
                    res = true;
                }
            }

            rs.close();
            st.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

        return res;

    }

    //Not tested
    public static ArrayList<Comment> getPostComments(BlogPost p) {
        ArrayList<Comment> list = new ArrayList<Comment>();

        Connection conn = createConnection();

        try {
            Statement st = conn.createStatement();

            String getPostCommentIdString = "SELECT * FROM BlogComment WHERE postid=?";
            PreparedStatement query2 = conn.prepareStatement(getPostCommentIdString);
            query2.setString(1, "" + getPostId(p));

            ResultSet rs = query2.executeQuery();
            /*
            String query = "SELECT * FROM BlogComment WHERE postid='" + getPostId(p) + "';";
            ResultSet rs = st.executeQuery(query);
            /*
             *
             */
            while (rs.next()) {
                list.add(new Comment(getUser(rs.getString("owner")), rs.getString("text"), rs.getDate("date"), p));
            }

            rs.close();
            st.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

        return list;
    }

    public static BlogPost getPost(User u, String title, Date date) {
        ArrayList<BlogPost> list = new ArrayList<BlogPost>();

        Connection conn = createConnection();

        try {
            Statement st = conn.createStatement();

            String getPostIdString = "SELECT * FROM BlogPost WHERE owner=? AND title=? AND date=?";
            PreparedStatement query = conn.prepareStatement(getPostIdString);
            query.setString(1, u.getUserName());
            query.setString(2, title);
            query.setString(3, "" + DateHelpers.utilDateToSqlDate(date));

            ResultSet rs = query.executeQuery();
            /*
            String query = "SELECT * FROM BlogPost WHERE owner='" + u.getUserName() + "' AND title='" + title + "' AND date='" + DateHelpers.utilDateToSqlDate(date) + "';";
            ResultSet rs = st.executeQuery(query);
             *
             *
             */
            while (rs.next()) {
                list.add(new BlogPost(rs.getString("title"), rs.getString("text"), rs.getDate("date"), getUser(rs.getString("owner")), rs.getString("picture")));
            }

            rs.close();
            st.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

        if (list.size() > 0) {
            return list.get(0);
        }
        return null;

    }

    public static BlogPost getPost(int postId) {
        BlogPost returnPost = new BlogPost();
        Connection conn = createConnection();

        try {
            Statement st = conn.createStatement();
            String queryString = "SELECT * FROM BlogPost WHERE blogid = ?";

            PreparedStatement query = conn.prepareStatement(queryString);

            query.setInt(1, postId);

            ResultSet rs = query.executeQuery();

            while (rs.next()) {
                returnPost = new BlogPost(rs.getInt("blogid"), rs.getString("title"), rs.getString("text"), rs.getDate("date"), getUser(rs.getString("owner")), rs.getString("picture"));
            }

            rs.close();
            st.close();
            conn.close();

        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return returnPost;
    }

    public static boolean activateUser(String code) {
        Connection conn = createConnection();
        boolean success = false;
        try {
            String getUser = "SELECT * FROM BlogUser WHERE code=?";
            PreparedStatement query = conn.prepareStatement(getUser);
            query.setString(1, code);
            ResultSet rs = query.executeQuery();
            if (rs.next()) {
                String updateActivationStatus = "UPDATE BlogUser SET active=? WHERE code=?";
                PreparedStatement query2 = conn.prepareStatement(updateActivationStatus);
                query2.setInt(1, 1);
                query2.setString(2, code);
                query2.execute();
                success = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return success;
    }

    public static boolean changeUserPassword(User u, String pwd) {
        Connection conn = createConnection();
        boolean success = false;
        try {
            String updateString = "UPDATE BlogUser SET password=? WHERE username=?";
            PreparedStatement query = conn.prepareStatement(updateString);
            query.setString(1, pwd);
            query.setString(2, u.getUserName());
            query.executeUpdate();
            success = true;     
        } catch (Exception e) {
            e.printStackTrace();
        }
        return success;
    }
    
    public static User getUserByEmail(String email) {
        ArrayList<User> users = getUsers();
        User result = null;

        for (User u : users) {
            if (u.getEmail().equals(email)) {
                result = u;
            }
        }
        return result;
    }        
}
